﻿Imports System.Data.OleDb
Imports System.Text.RegularExpressions
Public Class frmManageStaff

#Region "NotifyIcon"

    Private Sub frmManageStaff_Resize(sender As Object, e As EventArgs) Handles Me.Resize
        Try
            If Me.WindowState = FormWindowState.Minimized Then
                Me.Visible = False
                NotifyIcon.Visible = True
                NotifyIcon.ShowBalloonTip(1, "SkyCoffee", "SkyCoffee is Still Running!", ToolTipIcon.Info)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub NotifyIcon_MouseDoubleClick(sender As Object, e As MouseEventArgs) Handles NotifyIcon.MouseDoubleClick
        Try
            Me.Visible = True
            Me.WindowState = FormWindowState.Normal
            NotifyIcon.Visible = False
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub mnuLaunchApplication_Click(sender As Object, e As EventArgs) Handles mnuLaunchApplication.Click
        Try
            Me.Visible = True
            Me.WindowState = FormWindowState.Normal
            NotifyIcon.Visible = False
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub mnuExitApplication_Click(sender As Object, e As EventArgs) Handles mnuExitApplication.Click
        Application.Exit()
    End Sub
#End Region

    Dim ds As New DataSet()
    Dim intCurrentIndex As Integer = 0
    Dim da As New OleDbDataAdapter()
    Dim conn As New OleDbConnection()

    Dim dsData As New DataSet()
    Dim intCurrentIndexData As Integer = 0
    Dim daData As New OleDbDataAdapter()
    Dim connData As New OleDbConnection()

    Dim dsEmail As New DataSet()
    Dim intCurrentIndexEmail As Integer = 0
    Dim daEmail As New OleDbDataAdapter()
    Dim connEmail As New OleDbConnection()
    Public Sub FillFields()
        txtReferenceNumber.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffNumber").ToString()
        txtFirstName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffFirstName").ToString()
        txtSurname.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffSurname").ToString()
        cmbGender.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffGender").ToString()
        txtDateOfBirth.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffDateOfBirth").ToString()
        txtAge.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffAge").ToString()
        txtSelectPhoto.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffSelectPhoto").ToString()

        txtAddress.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffAddress").ToString()
        cmbCity.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffCity").ToString()
        txtPostalCode.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffPostalCode").ToString()
        txtEmailAddress.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffEmailAddress").ToString()
        txtPhone.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffPhone").ToString()
        txtMobile.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffMobile").ToString()
        txtEmergencyPhone.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffEmergencyPhone").ToString()
        txtEmergencyMobile.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffEmergencyMobile").ToString()

        txtHireDate.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffHireDate").ToString()
        txtContractEndDate.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffContractEndDate").ToString()
        txtCompensation.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffCompensation").ToString()
        cmbCompensationType.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffCompensationType").ToString()
        cmbShiftInformation.Value = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffShiftInformation").ToString()
        txtFunctionalArea.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffFunctionalArea").ToString()
        txtAvailability.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffAvailability").ToString()
        cmbEmploymentType.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffEmploymentType").ToString()

        txtEmergencyDoctorFullName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldEmergencyDoctorFullName").ToString()
        txtEmergencyDoctorPhone.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldEmergencyDoctorPhone").ToString()
        txtEmergencyPersonToNotify.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldEmergencyPersonToNotify").ToString()
        txtEmergencyPersonPhone.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldEmergencyPersonPhone").ToString()
        txtEmergencyRelationship.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldEmergencyRelationship").ToString()
        txtEmergencyAddress.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldEmergencyAddress").ToString()
        cmbEmergencyCity.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldEmergencyCity").ToString()
        txtEmergencyPostalCode.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldEmergencyPostalCode").ToString()

        txtStaffNotes.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldStaffNotes").ToString()
    End Sub
    Public Sub FillFieldsData()
        'Enter data into tblStaffRetrieveDataForSMS
        txtReferenceNumber.Text = dsData.Tables(0).Rows(intCurrentIndexData).Item("fldDataID").ToString()
        txtFirstName.Text = dsData.Tables(0).Rows(intCurrentIndexData).Item("fldDataFirstName").ToString()
        txtSurname.Text = dsData.Tables(0).Rows(intCurrentIndexData).Item("fldDataSurname").ToString()
        txtMobile.Text = dsData.Tables(0).Rows(intCurrentIndexData).Item("fldDataMobile").ToString()
    End Sub
    Public Sub FillFieldsEmail()
        'Enter data into tblStaffRetrieveDataForEmail
        txtReferenceNumber.Text = dsEmail.Tables(0).Rows(intCurrentIndexEmail).Item("fldDataID").ToString()
        txtFirstName.Text = dsEmail.Tables(0).Rows(intCurrentIndexEmail).Item("fldDataFirstName").ToString()
        txtSurname.Text = dsEmail.Tables(0).Rows(intCurrentIndexEmail).Item("fldDataSurname").ToString()
        txtEmailAddress.Text = dsEmail.Tables(0).Rows(intCurrentIndexEmail).Item("fldDataEmail").ToString()
    End Sub
    Public Sub AddNewRecord()
        'Clear form to add new record
        txtReferenceNumber.Text = ""
        txtFirstName.Text = ""
        txtSurname.Text = ""
        cmbGender.SelectedIndex = -1
        txtDateOfBirth.Text = ""
        txtAge.Text = ""
        picProfilePhoto.Image = Nothing
        txtSelectPhoto.Text = ""
        txtAddress.Text = ""
        cmbCity.SelectedIndex = -1
        txtPostalCode.Text = ""
        txtPhone.Text = ""
        txtMobile.Text = ""
        txtEmailAddress.Text = ""
        txtEmergencyPhone.Text = ""
        txtEmergencyMobile.Text = ""
        txtCompensation.Text = ""
        cmbCompensationType.SelectedIndex = -1
        cmbShiftInformation.Value = ""
        cmbShiftInformation.Text = ""
        txtFunctionalArea.Text = ""
        txtAvailability.Text = ""
        cmbEmploymentType.SelectedIndex = -1
        txtEmergencyDoctorFullName.Text = ""
        txtEmergencyDoctorPhone.Text = ""
        txtEmergencyPersonToNotify.Text = ""
        txtEmergencyPersonPhone.Text = ""
        txtEmergencyRelationship.Text = ""
        txtEmergencyAddress.Text = ""
        cmbEmergencyCity.SelectedIndex = -1
        txtEmergencyPostalCode.Text = ""
        txtStaffNotes.Text = ""

        txtFirstName.Focus()

        'Generate Random Reference Number
        Dim intIDLength As Integer  'length of ID
        Dim intCount As Integer     'variable to count to the value of intIDLength
        Dim intAsc As Integer       'random number to choose what the character will be

        Dim strResult As String     'value of the resulting ID

        Dim intNumber As Integer
        Dim intCharacter As Integer 'the resulting character from one cycle

        intIDLength = 4             'length of staff id

        Randomize()

        For intNumber = 1 To 1

            'number of random characters to generate
            For intCount = 1 To intIDLength


                'choose random character type to generate numbers
                intAsc = Int((1 * Rnd()) + 1)

                Select Case intAsc

                    Case 1
                        intCharacter = Int((Asc("9") - Asc("0") + 1) * Rnd() + Asc("0"))

                End Select

                'concatenate the resulting character to the previously generated character
                strResult = strResult & Chr(intCharacter)

                'repeat process if the value of "intCount" has not reached "intNumberDigits"
            Next intCount

            strResult = strResult

        Next intNumber

        'display the resulting id in the label
        txtReferenceNumber.Text = strResult

        'Display current data/time in TextBox
        txtDateOfBirth.Text = Date.Now
        txtHireDate.Text = Date.Now
        txtContractEndDate.Text = Date.Now
    End Sub

    Private Sub frmManageStaff_Load(sender As Object, e As EventArgs) Handles Me.Load
        'Loads frmMenu
        pnlIcons.Controls.Clear()
        pnlIcons.Controls.Add(frmMenu.pnlIcons)

        'Load ToolTips
        AdminToolTips()

        'Enable Editing
        conn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;" & _
                "Data Source=|DataDirectory|\SkyCoffee.mdb"

        da.SelectCommand = New OleDbCommand("SELECT * FROM tblStaff")
        da.SelectCommand.Connection = conn

        da.UpdateCommand = New OleDbCommand("UPDATE tblStaff SET fldStaffFirstName = @fldStaffFirstName, fldStaffSurname = @fldStaffSurname, fldStaffGender = @fldStaffGender, fldStaffDateOfBirth = @fldStaffDateOfBirth, fldStaffAge = @fldStaffAge, fldStaffSelectPhoto = @fldStaffSelectPhoto, fldStaffAddress = @fldStaffAddress, fldStaffCity = @fldStaffCity, fldStaffPostalCode = @fldStaffPostalCode, fldStaffEmailAddress = @fldStaffEmailAddress, fldStaffPhone = @fldStaffPhone, fldStaffMobile = @fldStaffMobile, fldStaffEmergencyPhone = @fldStaffEmergencyPhone, fldStaffEmergencyMobile = @fldStaffEmergencyMobile, fldStaffHireDate = @fldStaffHireDate, fldStaffContractEndDate = @fldStaffContractEndDate, fldStaffCompensation = @fldStaffCompensation, fldStaffCompensationType = @fldStaffCompensationType, fldStaffShiftInformation = @fldStaffShiftInformation, fldStaffFunctionalArea = @fldStaffFunctionalArea, fldStaffAvailability = @fldStaffAvailability, fldStaffEmploymentType = @fldStaffEmploymentType, fldEmergencyDoctorFullName = @fldEmergencyDoctorFullName, fldEmergencyDoctorPhone = @fldEmergencyDoctorPhone, fldEmergencyPersonToNotify = @fldEmergencyPersonToNotify, fldEmergencyPersonPhone = @fldEmergencyPersonPhone, fldEmergencyRelationship = @fldEmergencyRelationship, fldEmergencyAddress = @fldEmergencyAddress, fldEmergencyCity = @fldEmergencyCity, fldEmergencyPostalCode = @fldEmergencyPostalCode, fldStaffNotes = @fldStaffNotes WHERE fldStaffNumber = @fldStaffNumber")
        da.UpdateCommand.Connection = conn
        da.UpdateCommand.Parameters.Add("@fldStaffFirstName", OleDbType.VarChar, 40, "fldStaffFirstName")
        da.UpdateCommand.Parameters.Add("@fldStaffSurname", OleDbType.VarChar, 40, "fldStaffSurname")
        da.UpdateCommand.Parameters.Add("@fldStaffGender", OleDbType.VarChar, 40, "fldStaffGender")
        da.UpdateCommand.Parameters.Add("@fldStaffDateOfBirth", OleDbType.Date, 40, "fldStaffDateOfBirth")
        da.UpdateCommand.Parameters.Add("@fldStaffAge", OleDbType.VarChar, 40, "fldStaffAge")
        da.UpdateCommand.Parameters.Add("@fldStaffSelectPhoto", OleDbType.VarChar, 100, "fldStaffSelectPhoto")

        da.UpdateCommand.Parameters.Add("@fldStaffAddress", OleDbType.VarChar, 40, "fldStaffAddress")
        da.UpdateCommand.Parameters.Add("@fldStaffCity", OleDbType.VarChar, 40, "fldStaffCity")
        da.UpdateCommand.Parameters.Add("@fldStaffPostalCode", OleDbType.VarChar, 40, "fldStaffPostalCode")
        da.UpdateCommand.Parameters.Add("@fldStaffEmailAddress", OleDbType.VarChar, 40, "fldStaffEmailAddress")
        da.UpdateCommand.Parameters.Add("@fldStaffPhone", OleDbType.VarChar, 40, "fldStaffPhone")
        da.UpdateCommand.Parameters.Add("@fldStaffMobile", OleDbType.VarChar, 40, "fldStaffMobile")
        da.UpdateCommand.Parameters.Add("@fldStaffEmergencyPhone", OleDbType.VarChar, 40, "fldStaffEmergencyPhone")
        da.UpdateCommand.Parameters.Add("@fldStaffEmergencyMobile", OleDbType.VarChar, 40, "fldStaffEmergencyMobile")

        da.UpdateCommand.Parameters.Add("@fldStaffHireDate", OleDbType.VarChar, 40, "fldStaffHireDate")
        da.UpdateCommand.Parameters.Add("@fldStaffContractEndDate", OleDbType.VarChar, 40, "fldStaffContractEndDate")
        da.UpdateCommand.Parameters.Add("@fldStaffCompensation", OleDbType.VarChar, 40, "fldStaffCompensation")
        da.UpdateCommand.Parameters.Add("@fldStaffCompensationType", OleDbType.VarChar, 40, "fldStaffCompensationType")
        da.UpdateCommand.Parameters.Add("@fldStaffShiftInformation", OleDbType.VarChar, 40, "fldStaffShiftInformation")
        da.UpdateCommand.Parameters.Add("@fldStaffFunctionalArea", OleDbType.VarChar, 40, "fldStaffFunctionalArea")
        da.UpdateCommand.Parameters.Add("@fldStaffAvailability", OleDbType.VarChar, 40, "fldStaffAvailability")
        da.UpdateCommand.Parameters.Add("@fldStaffEmploymentType", OleDbType.VarChar, 40, "fldStaffEmploymentType")

        da.UpdateCommand.Parameters.Add("@fldEmergencyDoctorFullName", OleDbType.VarChar, 40, "fldEmergencyDoctorFullName")
        da.UpdateCommand.Parameters.Add("@fldEmergencyDoctorPhone", OleDbType.VarChar, 40, "fldEmergencyDoctorPhone")
        da.UpdateCommand.Parameters.Add("@fldEmergencyPersonToNotify", OleDbType.VarChar, 40, "fldEmergencyPersonToNotify")
        da.UpdateCommand.Parameters.Add("@fldEmergencyPersonPhone", OleDbType.VarChar, 40, "fldEmergencyPersonPhone")
        da.UpdateCommand.Parameters.Add("@fldEmergencyRelationship", OleDbType.VarChar, 40, "fldEmergencyRelationship")
        da.UpdateCommand.Parameters.Add("@fldEmergencyAddress", OleDbType.VarChar, 40, "fldEmergencyAddress")
        da.UpdateCommand.Parameters.Add("@fldEmergencyCity", OleDbType.VarChar, 40, "fldEmergencyCity")
        da.UpdateCommand.Parameters.Add("@fldEmergencyPostalCode", OleDbType.VarChar, 40, "fldEmergencyPostalCode")

        da.UpdateCommand.Parameters.Add("@fldStaffNotes", OleDbType.VarChar, 40, "fldStaffNotes")

        da.UpdateCommand.Parameters.Add("@fldStaffNumber", OleDbType.Numeric, 6, "fldStaffNumber") '.SourceVersion = DataRowVersion.Original

        da.InsertCommand = New OleDbCommand("INSERT INTO tblStaff(fldStaffFirstName, fldStaffSurname, fldStaffGender, fldStaffDateOfBirth, fldStaffAge, fldStaffSelectPhoto, fldStaffAddress, fldStaffCity, fldStaffPostalCode, fldStaffEmailAddress, fldStaffPhone, fldStaffMobile, fldStaffEmergencyPhone, fldStaffEmergencyMobile, fldStaffHireDate, fldStaffContractEndDate, fldStaffCompensation, fldStaffCompensationType, fldStaffShiftInformation, fldStaffFunctionalArea, fldStaffAvailability, fldStaffEmploymentType, fldEmergencyDoctorFullName, fldEmergencyDoctorPhone, fldEmergencyPersonToNotify, fldEmergencyPersonPhone, fldEmergencyRelationship, fldEmergencyAddress, fldEmergencyCity, fldEmergencyPostalCode, fldStaffNotes, fldStaffNumber) VALUES(@fldStaffFirstName,@fldStaffSurname,@fldStaffGender,@fldStaffDateOfBirth,@fldStaffAge,@fldStaffSelectPhoto,@fldStaffAddress,@fldStaffCity,@fldStaffPostalCode,@fldStaffEmailAddress,@fldStaffPhone,@fldStaffMobile,@fldStaffEmergencyPhone,@fldStaffEmergencyMobile,@fldStaffHireDate,@fldStaffContractEndDate,@fldStaffCompensation,@fldStaffCompensationType,@fldStaffShiftInformation,@fldStaffFunctionalArea,@fldStaffAvailability,@fldStaffEmploymentType,@fldEmergencyDoctorFullName,@fldEmergencyDoctorPhone,@fldEmergencyPersonToNotify,@fldEmergencyPersonPhone,@fldEmergencyRelationship,@fldEmergencyAddress,@fldEmergencyCity,@fldEmergencyPostalCode,@fldStaffNotes,@fldStaffNumber)")
        da.InsertCommand.Connection = conn
        da.InsertCommand.Parameters.Add("@fldStaffFirstName", OleDbType.VarChar, 40, "fldStaffFirstName")
        da.InsertCommand.Parameters.Add("@fldStaffSurname", OleDbType.VarChar, 40, "fldStaffSurname")
        da.InsertCommand.Parameters.Add("@fldStaffGender", OleDbType.VarChar, 40, "fldStaffGender")
        da.InsertCommand.Parameters.Add("@fldStaffDateOfBirth", OleDbType.Date, 40, "fldStaffDateOfBirth")
        da.InsertCommand.Parameters.Add("@fldStaffAge", OleDbType.VarChar, 40, "fldStaffAge")
        da.InsertCommand.Parameters.Add("@fldStaffSelectPhoto", OleDbType.VarChar, 40, "fldStaffSelectPhoto")

        da.InsertCommand.Parameters.Add("@fldStaffAddress", OleDbType.VarChar, 40, "fldStaffAddress")
        da.InsertCommand.Parameters.Add("@fldStaffCity", OleDbType.VarChar, 40, "fldStaffCity")
        da.InsertCommand.Parameters.Add("@fldStaffPostalCode", OleDbType.VarChar, 40, "fldStaffPostalCode")
        da.InsertCommand.Parameters.Add("@fldStaffEmailAddress", OleDbType.VarChar, 40, "fldStaffEmailAddress")
        da.InsertCommand.Parameters.Add("@fldStaffPhone", OleDbType.VarChar, 40, "fldStaffPhone")
        da.InsertCommand.Parameters.Add("@fldStaffMobile", OleDbType.VarChar, 40, "fldStaffMobile")
        da.InsertCommand.Parameters.Add("@fldStaffEmergencyPhone", OleDbType.VarChar, 40, "fldStaffEmergencyPhone")
        da.InsertCommand.Parameters.Add("@fldStaffEmergencyMobile", OleDbType.VarChar, 40, "fldStaffEmergencyMobile")

        da.InsertCommand.Parameters.Add("@fldStaffHireDate", OleDbType.VarChar, 40, "fldStaffHireDate")
        da.InsertCommand.Parameters.Add("@fldStaffContractEndDate", OleDbType.VarChar, 40, "fldStaffContractEndDate")
        da.InsertCommand.Parameters.Add("@fldStaffCompensation", OleDbType.VarChar, 40, "fldStaffCompensation")
        da.InsertCommand.Parameters.Add("@fldStaffCompensationType", OleDbType.VarChar, 40, "fldStaffCompensationType")
        da.InsertCommand.Parameters.Add("@fldStaffShiftInformation", OleDbType.VarChar, 40, "fldStaffShiftInformation")
        da.InsertCommand.Parameters.Add("@fldStaffFunctionalArea", OleDbType.VarChar, 40, "fldStaffFunctionalArea")
        da.InsertCommand.Parameters.Add("@fldStaffAvailability", OleDbType.VarChar, 40, "fldStaffAvailability")
        da.InsertCommand.Parameters.Add("@fldStaffEmploymentType", OleDbType.VarChar, 40, "fldStaffEmploymentType")

        da.InsertCommand.Parameters.Add("@fldEmergencyDoctorFullName", OleDbType.VarChar, 40, "fldEmergencyDoctorFullName")
        da.InsertCommand.Parameters.Add("@fldEmergencyDoctorPhone", OleDbType.VarChar, 40, "fldEmergencyDoctorPhone")
        da.InsertCommand.Parameters.Add("@fldEmergencyPersonToNotify", OleDbType.VarChar, 40, "fldEmergencyPersonToNotify")
        da.InsertCommand.Parameters.Add("@fldEmergencyPersonPhone", OleDbType.VarChar, 40, "fldEmergencyPersonPhone")
        da.InsertCommand.Parameters.Add("@fldEmergencyRelationship", OleDbType.VarChar, 40, "fldEmergencyRelationship")
        da.InsertCommand.Parameters.Add("@fldEmergencyAddress", OleDbType.VarChar, 40, "fldEmergencyAddress")
        da.InsertCommand.Parameters.Add("@fldEmergencyCity", OleDbType.VarChar, 40, "fldEmergencyCity")
        da.InsertCommand.Parameters.Add("@fldEmergencyPostalCode", OleDbType.VarChar, 40, "fldEmergencyPostalCode")

        da.InsertCommand.Parameters.Add("@fldStaffNotes", OleDbType.VarChar, 40, "fldStaffNotes")

        da.InsertCommand.Parameters.Add("@fldStaffNumber", OleDbType.Numeric, 6, "fldStaffNumber")

        da.DeleteCommand = New OleDbCommand("DELETE FROM tblStaff WHERE fldStaffNumber = @fldStaffNumber")
        da.DeleteCommand.Connection = conn
        da.DeleteCommand.Parameters.Add("@fldStaffNumber", OleDbType.Numeric, 6, "fldStaffNumber")

        da.Fill(ds)

        If ds.Tables(0).Rows.Count > 0 Then 'Check to see if the table is empty
            FillFields()
        Else
            MsgBox("No records exist in database")
        End If

        'Enter data into tblStaffRetrieveDataForSMS
        connData.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;" & _
                "Data Source=|DataDirectory|\SkyCoffee.mdb"

        daData.SelectCommand = New OleDbCommand("SELECT * FROM tblStaffRetrieveDataForSMS")
        daData.SelectCommand.Connection = connData

        daData.UpdateCommand = New OleDbCommand("UPDATE tblStaffRetrieveDataForSMS SET fldDataFirstName = @fldDataFirstName, fldDataSurname = @fldDataSurname, fldDataMobile = @fldDataMobile WHERE fldDataID = @fldDataID")
        daData.UpdateCommand.Connection = connData
        daData.UpdateCommand.Parameters.Add("@fldDataFirstName", OleDbType.VarChar, 40, "fldDataFirstName")
        daData.UpdateCommand.Parameters.Add("@fldDataSurname", OleDbType.VarChar, 40, "fldDataSurname")
        daData.UpdateCommand.Parameters.Add("@fldDataMobile", OleDbType.VarChar, 40, "fldDataMobile")
        daData.UpdateCommand.Parameters.Add("@fldDataID", OleDbType.Numeric, 6, "fldDataID")

        daData.InsertCommand = New OleDbCommand("INSERT INTO tblStaffRetrieveDataForSMS(fldDataFirstName, fldDataSurname, fldDataMobile, fldDataID) VALUES(@fldDataFirstName,@fldDataSurname,@fldDataMobile,@fldDataID)")
        daData.InsertCommand.Connection = connData
        daData.InsertCommand.Parameters.Add("@fldDataFirstName", OleDbType.VarChar, 40, "fldDataFirstName")
        daData.InsertCommand.Parameters.Add("@fldDataSurname", OleDbType.VarChar, 40, "fldDataSurname")
        daData.InsertCommand.Parameters.Add("@fldDataMobile", OleDbType.VarChar, 40, "fldDataMobile")
        daData.InsertCommand.Parameters.Add("@fldDataID", OleDbType.Numeric, 6, "fldDataID")

        daData.DeleteCommand = New OleDbCommand("DELETE FROM tblStaffRetrieveDataForSMS WHERE fldDataID = @fldDataID")
        daData.DeleteCommand.Connection = connData
        daData.DeleteCommand.Parameters.Add("@fldDataID", OleDbType.Numeric, 6, "fldDataID")

        daData.Fill(dsData)

        If dsData.Tables(0).Rows.Count > 0 Then 'Check to see if the table is empty
            FillFieldsData()
        Else
            MsgBox("No records exist in database")
        End If

        'Enter data into tblRetrieveDataForEmail
        connEmail.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;" & _
                "Data Source=|DataDirectory|\SkyCoffee.mdb"

        daEmail.SelectCommand = New OleDbCommand("SELECT * FROM tblStaffRetrieveDataForEmail")
        daEmail.SelectCommand.Connection = connEmail

        daEmail.UpdateCommand = New OleDbCommand("UPDATE tblStaffRetrieveDataForEmail SET fldDataFirstName = @fldDataFirstName, fldDataSurname = @fldDataSurname, fldDataEmail = @fldDataEmail WHERE fldDataID = @fldDataID")
        daEmail.UpdateCommand.Connection = connEmail
        daEmail.UpdateCommand.Parameters.Add("@fldDataFirstName", OleDbType.VarChar, 40, "fldDataFirstName")
        daEmail.UpdateCommand.Parameters.Add("@fldDataSurname", OleDbType.VarChar, 40, "fldDataSurname")
        daEmail.UpdateCommand.Parameters.Add("@fldDataEmail", OleDbType.VarChar, 40, "fldDataEmail")
        daEmail.UpdateCommand.Parameters.Add("@fldDataID", OleDbType.Numeric, 6, "fldDataID")

        daEmail.InsertCommand = New OleDbCommand("INSERT INTO tblStaffRetrieveDataForEmail(fldDataFirstName, fldDataSurname, fldDataEmail, fldDataID) VALUES(@fldDataFirstName,@fldDataSurname,@fldDataEmail,@fldDataID)")
        daEmail.InsertCommand.Connection = connEmail
        daEmail.InsertCommand.Parameters.Add("@fldDataFirstName", OleDbType.VarChar, 40, "fldDataFirstName")
        daEmail.InsertCommand.Parameters.Add("@fldDataSurname", OleDbType.VarChar, 40, "fldDataSurname")
        daEmail.InsertCommand.Parameters.Add("@fldDataEmail", OleDbType.VarChar, 40, "fldDataEmail")
        daEmail.InsertCommand.Parameters.Add("@fldDataID", OleDbType.Numeric, 6, "fldDataID")

        daEmail.DeleteCommand = New OleDbCommand("DELETE FROM tblStaffRetrieveDataForEmail WHERE fldDataID = @fldDataID")
        daEmail.DeleteCommand.Connection = connEmail
        daEmail.DeleteCommand.Parameters.Add("@fldDataID", OleDbType.Numeric, 6, "fldDataID")

        daEmail.Fill(dsEmail)

        If dsEmail.Tables(0).Rows.Count > 0 Then 'Check to see if the table is empty
            FillFieldsEmail()
        Else
            MsgBox("No records exist in database")
        End If

        'Load tblShifts into ComboBox
        Dim connString As String
        Dim connShiftInformation As New System.Data.OleDb.OleDbConnection()
        Dim daShiftInformation As New System.Data.OleDb.OleDbDataAdapter()
        Dim dsShiftInformation As DataSet

        connString = "Provider=Microsoft.jet.oledb.4.0;" & _
            "Data Source=|DataDirectory|\SkyCoffee.mdb"

        connShiftInformation = New System.Data.OleDb.OleDbConnection(connString)
        connShiftInformation.Open()
        daShiftInformation = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM tblShifts", connShiftInformation)
        dsShiftInformation = New DataSet("Combo")
        daShiftInformation.Fill(dsShiftInformation, "Combo")
        cmbShiftInformation.DataSource = dsShiftInformation.Tables("Combo")

        cmbShiftInformation.LoadData() 'Load the ComboBox functions

    End Sub

    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        'Validate that all fields have been filled in correctly
        Dim patternEmailAddress As String = "[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|com|org|net|biz|info|name)\b"
        Dim regexEmailAddress As New Regex(patternEmailAddress)

        If IsNumeric(txtCompensation.Text) = False Then
            MsgBox("Please enter a valid compensation")
            txtCompensation.Focus()
        ElseIf IsNumeric(txtPostalCode.Text) = False Or txtPostalCode.Text.Length > 4 Then
            MsgBox("Please enter a valid postal code")
            txtPostalCode.Focus()
        ElseIf IsNumeric(txtEmergencyPhone.Text) = False Or txtEmergencyPhone.Text.Length > 8 Then
            MsgBox("Please enter a valid Emergency Phone")
            txtEmergencyPhone.Focus()
        ElseIf IsNumeric(txtEmergencyMobile.Text) = False Or txtEmergencyMobile.Text.Length > 8 Then
            MsgBox("Please enter a valid Emergency Mobile")
            txtEmergencyMobile.Focus()
        ElseIf IsNumeric(txtEmergencyDoctorPhone.Text) = False Or txtEmergencyDoctorPhone.Text.Length > 8 Then
            MsgBox("Please enter a valid Emergency Doctor Phone")
            txtEmergencyDoctorPhone.Focus()
        ElseIf IsNumeric(txtEmergencyPersonPhone.Text) = False Or txtEmergencyPersonPhone.Text.Length > 8 Then
            MsgBox("Please enter a valid Emergency Person Phone")
            txtEmergencyPersonPhone.Focus()
        ElseIf IsNumeric(txtEmergencyPostalCode.Text) = False Or txtEmergencyPostalCode.Text.Length > 4 Then
            MsgBox("Please enter a valid Emergency Postal Code")
            txtEmergencyPostalCode.Focus()
        ElseIf regexEmailAddress.IsMatch(txtEmailAddress.Text) = False Then
            MsgBox("Please enter a valid email address")
            txtEmailAddress.Focus()
        ElseIf IsNumeric(txtPhone.Text) = False Or txtPhone.Text.Length > 8 Then
            MsgBox("Please enter a valid phone number")
            txtPhone.Focus()
        ElseIf IsNumeric(txtMobile.Text) = False Or txtMobile.Text.Length > 8 Then
            MsgBox("Please enter a valid mobile phone number")
            txtMobile.Focus()
        ElseIf txtFirstName.Text = "" Or txtSurname.Text = "" Or cmbGender.Text = "" Or txtDateOfBirth.Text = "" Or txtSelectPhoto.Text = "" Or txtAddress.Text = "" Or cmbCity.Text = "" Or txtPostalCode.Text = "" Or txtPhone.Text = "" Or txtMobile.Text = "" Or txtEmailAddress.Text = "" Or txtEmergencyPhone.Text = "" Or txtEmergencyMobile.Text = "" Or txtHireDate.Text = "" Or txtCompensation.Text = "" Or cmbCompensationType.Text = "" Or cmbShiftInformation.Text = "" Or txtAvailability.Text = "" Or txtFunctionalArea.Text = "" Or cmbEmploymentType.Text = "" Or txtEmergencyDoctorFullName.Text = "" Or txtEmergencyDoctorPhone.Text = "" Or txtEmergencyPersonToNotify.Text = "" Or txtEmergencyPersonPhone.Text = "" Or txtEmergencyRelationship.Text = "" Or txtEmergencyAddress.Text = "" Or cmbEmergencyCity.Text = "" Or txtEmergencyPostalCode.Text = "" Or txtStaffNotes.Text = "" Then
            MsgBox("Please fill in all of the fields")
        Else

            'Calculate Age from Date of Birth - 08/19/1991 12:00:00 AM
            Dim strDOB As String
            Dim intAge As Integer

            strDOB = txtDateOfBirth.Text
            intAge = Math.Floor(DateDiff(DateInterval.Month, DateValue(strDOB), Now()) / 12)

            txtAge.Text = intAge

            'File Operations: Copy Files selected as Photos
            '#Photos
            Dim strPFileToCopy As String
            Dim strPNewCopy As String

            strPFileToCopy = txtSelectPhoto.Text
            strPNewCopy = Application.StartupPath & System.IO.Path.GetFileName(txtSelectPhoto.Text)

            If System.IO.File.Exists(strPFileToCopy) = True Then
                System.IO.File.Copy(strPFileToCopy, strPNewCopy)
                MsgBox("File Copied Successfully.")
            Else
                MsgBox("Cannot locate the file specified.")
            End If

            'Database Operations: Add all data to Access Database
            Dim dr As DataRow

            dr = ds.Tables(0).NewRow()  'Gets a reference to a new row.
            dr("fldStaffNumber") = txtReferenceNumber.Text

            dr("fldStaffFirstName") = txtFirstName.Text
            dr("fldStaffSurname") = txtSurname.Text
            dr("fldStaffGender") = cmbGender.Text
            dr("fldStaffDateOfBirth") = txtDateOfBirth.Text
            dr("fldStaffAge") = txtAge.Text
            dr("fldStaffSelectPhoto") = txtSelectPhoto.Text

            dr("fldStaffAddress") = txtAddress.Text
            dr("fldStaffCity") = cmbCity.Text
            dr("fldStaffPostalCode") = txtPostalCode.Text
            dr("fldStaffEmailAddress") = txtEmailAddress.Text
            dr("fldStaffPhone") = txtPhone.Text
            dr("fldStaffMobile") = txtMobile.Text
            dr("fldStaffEmergencyPhone") = txtEmergencyPhone.Text
            dr("fldStaffEmergencyMobile") = txtEmergencyMobile.Text

            dr("fldStaffHireDate") = txtHireDate.Text
            dr("fldStaffContractEndDate") = txtContractEndDate.Text
            dr("fldStaffCompensation") = txtCompensation.Text
            dr("fldStaffCompensationType") = cmbCompensationType.Text
            dr("fldStaffShiftInformation") = cmbShiftInformation.Value
            dr("fldStaffFunctionalArea") = txtFunctionalArea.Text
            dr("fldStaffAvailability") = txtAvailability.Text
            dr("fldStaffEmploymentType") = cmbEmploymentType.Text

            dr("fldEmergencyDoctorFullName") = txtEmergencyDoctorFullName.Text
            dr("fldEmergencyDoctorPhone") = txtEmergencyDoctorPhone.Text
            dr("fldEmergencyPersonToNotify") = txtEmergencyPersonToNotify.Text
            dr("fldEmergencyPersonPhone") = txtEmergencyPersonPhone.Text
            dr("fldEmergencyRelationship") = txtEmergencyRelationship.Text
            dr("fldEmergencyAddress") = txtEmergencyAddress.Text
            dr("fldEmergencyCity") = cmbEmergencyCity.Text
            dr("fldEmergencyPostalCode") = txtEmergencyPostalCode.Text

            dr("fldStaffNotes") = txtStaffNotes.Text

            ds.Tables(0).Rows.Add(dr)
            da.Update(ds)
            ds.AcceptChanges()

            'Database Operations: Add all data to Access Database tblRetrieveDataForSMS
            Dim drData As DataRow

            drData = dsData.Tables(0).NewRow()  'Gets a reference to a new row.
            drData("fldDataID") = txtReferenceNumber.Text

            drData("fldDataFirstName") = txtFirstName.Text
            drData("fldDataSurname") = txtSurname.Text
            drData("fldDataMobile") = txtMobile.Text

            dsData.Tables(0).Rows.Add(drData)
            daData.Update(dsData)
            dsData.AcceptChanges()

            'Database Operations: Add all data to Access Database tblRetrieveDataForEmail
            Dim drEmail As DataRow

            drEmail = dsEmail.Tables(0).NewRow()  'Gets a reference to a new row.
            drEmail("fldDataID") = txtReferenceNumber.Text

            drEmail("fldDataFirstName") = txtFirstName.Text
            drEmail("fldDataSurname") = txtSurname.Text
            drEmail("fldDataEmail") = txtEmailAddress.Text

            dsEmail.Tables(0).Rows.Add(drEmail)
            daEmail.Update(dsEmail)
            dsEmail.AcceptChanges()

            AddNewRecord()

            MsgBox("Record added successfully")

        End If

    End Sub

    Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click
        AddNewRecord()
    End Sub

    Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click
        If ds.Tables(0).Rows.Count > 1 Or dsData.Tables(0).Rows.Count > 1 Then
            'Since 0 is the first row
            intCurrentIndex = 0
            FillFields()
        ElseIf ds.Tables(0).Rows.Count = 0 Or dsData.Tables(0).Rows.Count = 0 Then
            MsgBox("No records exist in database")
        Else
            MessageBox.Show("You are already at the first record.")
        End If
    End Sub

    Private Sub btnPrevious_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click
        If intCurrentIndex > 0 Then  'We move back only if we're not at the first row.
            intCurrentIndex = intCurrentIndex - 1  'Subtract one from the current index.
            FillFields()
        ElseIf ds.Tables(0).Rows.Count = 0 Or dsData.Tables(0).Rows.Count = 0 Then
            MsgBox("No records exist in database")
        Else
            MessageBox.Show("You are already at the first record.")
        End If
    End Sub

    Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
        If intCurrentIndex < ds.Tables(0).Rows.Count - 1 Then 'ds.Tables(0).Rows.Count - 1 is the index for the last row
            intCurrentIndex = intCurrentIndex + 1  'Add one to the current index.
            FillFields()
        ElseIf ds.Tables(0).Rows.Count = 0 Or dsData.Tables(0).Rows.Count = 0 Then
            MsgBox("No records exist in database")
        Else
            MessageBox.Show("You are already at the last record.")
        End If
    End Sub

    Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click
        If ds.Tables(0).Rows.Count > 1 Or dsData.Tables(0).Rows.Count > 1 Then
            intCurrentIndex = ds.Tables(0).Rows.Count - 1 'ds.Tables(0).Rows.Count - 1 is the index for the last row
            FillFields()
        ElseIf ds.Tables(0).Rows.Count = 0 Or dsData.Tables(0).Rows.Count = 0 Then
            MsgBox("No records exist in database")
        Else
            MessageBox.Show("You are already at the last record.")
        End If
    End Sub

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        'Validate that all fields have been filled in correctly
        Dim patternEmailAddress As String = "[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|com|org|net|biz|info|name)\b"
        Dim regexEmailAddress As New Regex(patternEmailAddress)

        If IsNumeric(txtCompensation.Text) = False Then
            MsgBox("Please enter a valid compensation")
            txtCompensation.Focus()
        ElseIf IsNumeric(txtPostalCode.Text) = False Or txtPostalCode.Text.Length > 4 Then
            MsgBox("Please enter a valid postal code")
            txtPostalCode.Focus()
        ElseIf IsNumeric(txtEmergencyPhone.Text) = False Or txtEmergencyPhone.Text.Length > 8 Then
            MsgBox("Please enter a valid Emergency Phone")
            txtEmergencyPhone.Focus()
        ElseIf IsNumeric(txtEmergencyMobile.Text) = False Or txtEmergencyMobile.Text.Length > 8 Then
            MsgBox("Please enter a valid Emergency Mobile")
            txtEmergencyMobile.Focus()
        ElseIf IsNumeric(txtEmergencyDoctorPhone.Text) = False Or txtEmergencyDoctorPhone.Text.Length > 8 Then
            MsgBox("Please enter a valid Emergency Doctor Phone")
            txtEmergencyDoctorPhone.Focus()
        ElseIf IsNumeric(txtEmergencyPersonPhone.Text) = False Or txtEmergencyPersonPhone.Text.Length > 8 Then
            MsgBox("Please enter a valid Emergency Person Phone")
            txtEmergencyPersonPhone.Focus()
        ElseIf IsNumeric(txtEmergencyPostalCode.Text) = False Or txtEmergencyPostalCode.Text.Length > 4 Then
            MsgBox("Please enter a valid Emergency Postal Code")
            txtEmergencyPostalCode.Focus()
        ElseIf regexEmailAddress.IsMatch(txtEmailAddress.Text) = False Then
            MsgBox("Please enter a valid email address")
            txtEmailAddress.Focus()
        ElseIf IsNumeric(txtPhone.Text) = False Or txtPhone.Text.Length > 8 Then
            MsgBox("Please enter a valid phone number")
            txtPhone.Focus()
        ElseIf IsNumeric(txtMobile.Text) = False Or txtMobile.Text.Length > 8 Then
            MsgBox("Please enter a valid mobile phone number")
            txtMobile.Focus()
        ElseIf txtFirstName.Text = "" Or txtSurname.Text = "" Or cmbGender.Text = "" Or txtDateOfBirth.Text = "" Or txtAge.Text = "" Or txtAddress.Text = "" Or cmbCity.Text = "" Or txtPostalCode.Text = "" Or txtPhone.Text = "" Or txtMobile.Text = "" Or txtEmailAddress.Text = "" Or txtEmergencyPhone.Text = "" Or txtEmergencyMobile.Text = "" Or txtHireDate.Text = "" Or txtCompensation.Text = "" Or cmbCompensationType.Text = "" Or cmbShiftInformation.Text = "" Or txtAvailability.Text = "" Or txtFunctionalArea.Text = "" Or cmbEmploymentType.Text = "" Or txtEmergencyDoctorFullName.Text = "" Or txtEmergencyDoctorPhone.Text = "" Or txtEmergencyPersonToNotify.Text = "" Or txtEmergencyPersonPhone.Text = "" Or txtEmergencyRelationship.Text = "" Or txtEmergencyAddress.Text = "" Or cmbEmergencyCity.Text = "" Or txtEmergencyPostalCode.Text = "" Or txtStaffNotes.Text = "" Then
            MsgBox("Please fill in all of the fields")
        Else

            'Calculate Age from Date of Birth - 08/19/1991 12:00:00 AM
            Dim strDOB As String
            Dim intAge As Integer

            strDOB = txtDateOfBirth.Text
            intAge = Math.Floor(DateDiff(DateInterval.Month, DateValue(strDOB), Now()) / 12)

            intAge = txtAge.Text

            'Database Operations: Add all data to Access Database
            Dim dr As DataRow

            dr = ds.Tables(0).Rows(intCurrentIndex) 'This gets a reference to the row currently being edited
            dr.BeginEdit()
            dr("fldStaffFirstName") = txtFirstName.Text
            dr("fldStaffSurname") = txtSurname.Text
            dr("fldStaffGender") = cmbGender.Text
            dr("fldStaffDateOfBirth") = txtDateOfBirth.Text
            dr("fldStaffAge") = txtAge.Text
            dr("fldStaffSelectPhoto") = txtSelectPhoto.Text

            dr("fldStaffAddress") = txtAddress.Text
            dr("fldStaffCity") = cmbCity.Text
            dr("fldStaffPostalCode") = txtPostalCode.Text
            dr("fldStaffEmailAddress") = txtEmailAddress.Text
            dr("fldStaffPhone") = txtPhone.Text
            dr("fldStaffMobile") = txtMobile.Text
            dr("fldStaffEmergencyPhone") = txtEmergencyPhone.Text
            dr("fldStaffEmergencyMobile") = txtEmergencyMobile.Text

            dr("fldStaffHireDate") = txtHireDate.Text
            dr("fldStaffContractEndDate") = txtContractEndDate.Text
            dr("fldStaffCompensation") = txtCompensation.Text
            dr("fldStaffCompensationType") = cmbCompensationType.Text
            dr("fldStaffShiftInformation") = cmbShiftInformation.Value
            dr("fldStaffFunctionalArea") = txtFunctionalArea.Text
            dr("fldStaffAvailability") = txtAvailability.Text
            dr("fldStaffEmploymentType") = cmbEmploymentType.Text

            dr("fldEmergencyDoctorFullName") = txtEmergencyDoctorFullName.Text
            dr("fldEmergencyDoctorPhone") = txtEmergencyDoctorPhone.Text
            dr("fldEmergencyPersonToNotify") = txtEmergencyPersonToNotify.Text
            dr("fldEmergencyPersonPhone") = txtEmergencyPersonPhone.Text
            dr("fldEmergencyRelationship") = txtEmergencyRelationship.Text
            dr("fldEmergencyAddress") = txtEmergencyAddress.Text
            dr("fldEmergencyCity") = cmbEmergencyCity.Text
            dr("fldEmergencyPostalCode") = txtEmergencyPostalCode.Text

            dr("fldStaffNotes") = txtStaffNotes.Text
            dr.EndEdit()

            da.Update(ds)  'Ask the dataadapter to call the UpdateCommand and update the database
            ds.AcceptChanges() 'Commits the change to the dataset.

            'Database Operations: Add all data to Access Database tblRetrieveDataForSMS
            Dim drData As DataRow

            drData = dsData.Tables(0).Rows(intCurrentIndexData) 'This gets a reference to the row currently being edited
            drData.BeginEdit()
            drData = dsData.Tables(0).NewRow()  'Gets a reference to a new row.
            drData("fldDataID") = txtReferenceNumber.Text

            drData("fldDataFirstName") = txtFirstName.Text
            drData("fldDataSurname") = txtSurname.Text
            drData("fldDataMobile") = txtMobile.Text
            drData.EndEdit()

            daData.Update(dsData)  'Ask the dataadapter to call the UpdateCommand and update the database
            dsData.AcceptChanges() 'Commits the change to the dataset.

            'Database Operations: Add all data to Access Database tblRetrieveDataForEmail
            Dim drEmail As DataRow

            drEmail = dsEmail.Tables(0).Rows(intCurrentIndexEmail) 'This gets a reference to the row currently being edited
            drEmail.BeginEdit()
            drEmail = dsEmail.Tables(0).NewRow()  'Gets a reference to a new row.
            drEmail("fldDataID") = txtReferenceNumber.Text

            drEmail("fldDataFirstName") = txtFirstName.Text
            drEmail("fldDataSurname") = txtSurname.Text
            drEmail("fldDataEmail") = txtEmailAddress.Text
            drEmail.EndEdit()

            daEmail.Update(dsEmail)  'Ask the dataadapter to call the UpdateCommand and update the database
            dsEmail.AcceptChanges() 'Commits the change to the dataset.

            txtFirstName.Focus()

            MsgBox("Record updated successfully")
        End If
    End Sub

    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        Dim MsgBoxDelete As MsgBoxResult = MsgBox("Are You Sure You Wish To Delete This ?", MsgBoxStyle.YesNo)

        If ds.Tables(0).Rows.Count > 0 Then

            If MsgBoxDelete = MsgBoxResult.Yes Then

                Dim dr As DataRow

                dr = ds.Tables(0).Rows(intCurrentIndex)
                dr.Delete() 'Delete the row

                da.Update(ds)
                ds.AcceptChanges()

                'tblRetrieveDataForSMS
                Dim drData As DataRow

                drData = dsData.Tables(0).Rows(intCurrentIndexData)
                drData.Delete() 'Delete the row

                daData.Update(dsData)
                dsData.AcceptChanges()

                'tblRetrieveDataForEmail
                Dim drEmail As DataRow

                drEmail = dsEmail.Tables(0).Rows(intCurrentIndexEmail)
                drEmail.Delete() 'Delete the row

                daEmail.Update(dsEmail)
                dsEmail.AcceptChanges()

                MsgBox("Record removed successfully")

            Else
                MsgBox("Record not removed")
            End If
        ElseIf ds.Tables(0).Rows.Count = 0 Then
            MsgBox("No records exist in database")
        End If
    End Sub

    Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
        AddNewRecord()
    End Sub

    Private Sub txtDateOfBirth_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtDateOfBirth.KeyPress
        Select Case e.KeyChar
            Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "/", ":", "A", "P", "M", " ", vbBack '// your pre-selected Characters and Backspace.
                e.Handled = False '// allow.
            Case Else
                e.Handled = True '// block.

                MsgBox("You can only enter Numbers in this TextBox")
                txtDateOfBirth.Focus()
        End Select
    End Sub

    Private Sub btnSelectPhoto_Click(sender As Object, e As EventArgs) Handles btnSelectPhoto.Click
        ofdSelectPhoto.Title = "Please select a Photo"
        ofdSelectPhoto.InitialDirectory = "C:"
        ofdSelectPhoto.Filter = "Jpg/Gif/Png Files (*.jpg,*.gif,*.png)|*.jpg;*.gif;*.png"
        ofdSelectPhoto.FilterIndex = 1

        ofdSelectPhoto.ShowDialog()
    End Sub

    Private Sub ofdSelectPhoto_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles ofdSelectPhoto.FileOk
        Dim strm As System.IO.Stream
        strm = ofdSelectPhoto.OpenFile()
        txtSelectPhoto.Text = ofdSelectPhoto.FileName.ToString()
        If Not (strm Is Nothing) Then
            'resize photo to fit into picProfilePhoto
            Dim imgOrg As Bitmap
            Dim imgShow As Bitmap
            Dim g As Graphics
            Dim divideBy, divideByH, divideByW As Double
            imgOrg = DirectCast(Bitmap.FromFile(ofdSelectPhoto.FileName), Bitmap)

            divideByW = imgOrg.Width / picProfilePhoto.Width
            divideByH = imgOrg.Height / picProfilePhoto.Height
            If divideByW > 1 Or divideByH > 1 Then
                If divideByW > divideByH Then
                    divideBy = divideByW
                Else
                    divideBy = divideByH
                End If

                imgShow = New Bitmap(CInt(CDbl(imgOrg.Width) / divideBy), CInt(CDbl(imgOrg.Height) / divideBy))
                imgShow.SetResolution(imgOrg.HorizontalResolution, imgOrg.VerticalResolution)
                g = Graphics.FromImage(imgShow)
                g.InterpolationMode = Drawing2D.InterpolationMode.HighQualityBicubic
                g.DrawImage(imgOrg, New Rectangle(0, 0, CInt(CDbl(imgOrg.Width) / divideBy), CInt(CDbl(imgOrg.Height) / divideBy)), 0, 0, imgOrg.Width, imgOrg.Height, GraphicsUnit.Pixel)
                g.Dispose()
            Else
                imgShow = New Bitmap(imgOrg.Width, imgOrg.Height)
                imgShow.SetResolution(imgOrg.HorizontalResolution, imgOrg.VerticalResolution)
                g = Graphics.FromImage(imgShow)
                g.InterpolationMode = Drawing2D.InterpolationMode.HighQualityBicubic
                g.DrawImage(imgOrg, New Rectangle(0, 0, imgOrg.Width, imgOrg.Height), 0, 0, imgOrg.Width, imgOrg.Height, GraphicsUnit.Pixel)
                g.Dispose()
            End If
            imgOrg.Dispose()

            picProfilePhoto.Image = imgShow

            strm.Close()
            MsgBox("Photo Selected")
        Else
            MsgBox("You did not select a Photo")
        End If
    End Sub

    Private Sub txtSelectPhoto_KeyDown(sender As Object, e As KeyEventArgs) Handles txtSelectPhoto.KeyDown
        'Suppress ctrl-c and ctrl-v
        If e.Control AndAlso (e.KeyCode = Keys.V OrElse e.KeyCode = Keys.C) Then e.SuppressKeyPress = True
    End Sub

End Class